﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ARA.Domain.Services
{
    public class QueryService
    {
        public List<Entities.Batch> QueryBatchList()
        {
            using (ARADbContext dbContext = new ARADbContext())
            {
                return dbContext.Set<Entities.Batch>()
                    .OrderBy(p => p.CreatedTime)
                    .ToList();
            }
        }

        public List<Entities.Record> QueryRecordList(Guid batchId)
        {
            using(ARADbContext dbContext = new ARADbContext())
            {
                return dbContext.Set<Entities.Record>().Where(p => p.BatchId == batchId)
                    .OrderBy(p => p.Index)
                    .ToList();
            }
        }

        /// <summary>
        /// 登录时间 覆盖到loginDate的数据
        /// </summary>
        /// <param name="batchId"></param>
        /// <param name="loginDate"></param>
        /// <returns></returns>
        public List<Entities.Record> QueryRecordList(Guid batchId, DateTime loginDate)
        {
            using (ARADbContext dbContext = new ARADbContext())
            {
                DateTime dtBegin = new DateTime(loginDate.Year, loginDate.Month, loginDate.Day, 0, 0, 0);
                DateTime dtEnd = new DateTime(loginDate.Year, loginDate.Month, loginDate.Day, 23, 59, 59);
                return dbContext.Set<Entities.Record>().Where(p => p.BatchId == batchId
                    && (p.LoginTime >= dtBegin || p.LogoutTime >= dtBegin)
                    && (p.LogoutTime <= dtEnd || p.LoginTime <= dtEnd))
                    .OrderBy(p => p.Index)
                    .ToList();
            }
        }

        public List<Entities.Record> QueryRecordList(Guid batchId, DateTime beginDate, DateTime endDate)
        {
            using (ARADbContext dbContext = new ARADbContext())
            {
                DateTime dtBegin = new DateTime(beginDate.Year, beginDate.Month, beginDate.Day, 0, 0, 0);
                DateTime dtEnd = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
                return dbContext.Set<Entities.Record>().Where(p => p.BatchId == batchId
                    && (p.LoginTime >= dtBegin || p.LogoutTime >= dtBegin)
                    && (p.LogoutTime <= dtEnd || p.LoginTime <= dtEnd))
                    .OrderBy(p => p.Account)
                    .ToList();
            }
        }

        public List<Entities.Record> QueryRecordList(Guid batchId, Guid departmentId,DateTime beginDate, DateTime endDate)
        {
            using (ARADbContext dbContext = new ARADbContext())
            {
                DateTime dtBegin = new DateTime(beginDate.Year, beginDate.Month, beginDate.Day, 0, 0, 0);
                DateTime dtEnd = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
                return dbContext.Database.SqlQuery<Entities.Record>("exec [sp_ara_querybydepartment] @batchid={0},@departmentid={1},@begintime={2},@endtime={3}", batchId, departmentId, dtBegin, dtEnd).ToList();
            }
        }

        public List<Entities.Record> QueryRecordList(Guid batchId, string name, DateTime beginDate, DateTime endDate)
        {
            using (ARADbContext dbContext = new ARADbContext())
            {
                DateTime dtBegin = new DateTime(beginDate.Year, beginDate.Month, beginDate.Day, 0, 0, 0);
                DateTime dtEnd = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
                return dbContext.Database.SqlQuery<Entities.Record>("exec [sp_ara_querybyperson] @batchid={0},@name={1},@begintime={2},@endtime={3}", batchId, name, dtBegin, dtEnd).ToList();
            }
        }

        public List<Entities.Department> QueryDepartmentList()
        {
            using(ARADbContext dbContext = new ARADbContext())
            {
                return dbContext.Set<Entities.Department>().ToList();
            }
        }

        public List<Entities.Person> QueryPersonList(Guid departmentId)
        {
            using(ARADbContext dbContext = new ARADbContext())
            {
                return dbContext.Set<Entities.Person>().Where(p => p.DepartmentId == departmentId).ToList();
            }
        }
    }
}
